SQL Cheatsheet

Data Definition Language (DDL) Commands

Statement	Description / Example Syntax
CREATE DATABASE	Creates a new database. CREATE DATABASE database_name;
CREATE TABLE	Creates a new table. CREATE TABLE table_name (   column1 datatype [constraints],   column2 datatype [constraints],   ... );
ALTER TABLE	Modifies an existing table’s structure. ALTER TABLE table_name ADD column_name datatype; ALTER TABLE table_name MODIFY column_name new_datatype; ALTER TABLE table_name DROP COLUMN column_name;
DROP TABLE	Deletes a table and all its data permanently. DROP TABLE table_name;
TRUNCATE TABLE	Removes all rows from a table without logging individual row deletions (faster than DELETE). TRUNCATE TABLE table_name;
RENAME (TABLE)	Renames an existing table. ALTER TABLE old_table_name RENAME TO new_table_name;

Data Manipulation Language (DML) Commands

Statement	Description / Example Syntax
INSERT	Inserts new rows into a table. INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
UPDATE	Modifies existing rows in a table. UPDATE table_name SET column1 = value1,    column2 = value2 WHERE condition;
DELETE	Deletes rows from a table based on a condition. DELETE FROM table_name WHERE condition;
MERGE	Merges two tables (insert, update, or delete rows in a target based on a source). MERGE INTO target_table USING source_table ON (target_table.key = source_table.key) WHEN MATCHED THEN   UPDATE SET target_table.col = source_table.col WHEN NOT MATCHED THEN   INSERT (col1, col2) VALUES (source_table.col1, source_table.col2);

Data Query Language (DQL) – SELECT & Query Clauses

Clause/Keyword	Description / Example Syntax
SELECT	Retrieves data from a database. SELECT column1, column2 FROM table_name; SELECT * FROM table_name; (all columns)
FROM	Specifies which table(s) the data is coming from.
WHERE	Filters rows based on a condition. SELECT * FROM table_name WHERE condition;
AND / OR / NOT	Logical operators used within WHERE. SELECT * FROM table_name WHERE condition1 AND condition2;
ORDER BY	Sorts the result set by specified column(s). SELECT * FROM table_name ORDER BY column1 [ASC|DESC];
GROUP BY	Groups rows sharing certain values so that aggregate functions can be applied to each group. SELECT column, COUNT(*) FROM table_name GROUP BY column;
HAVING	Filters groups after GROUP BY. SELECT column, COUNT(*) FROM table_name GROUP BY column HAVING COUNT(*) > 1;
LIMIT / OFFSET	Restricts the number of rows returned / Skips a number of rows. SELECT * FROM table_name LIMIT 10 OFFSET 20;
DISTINCT	Selects unique values. SELECT DISTINCT column FROM table_name;
ALIASES	Renames a column or table in the query. SELECT column AS alias_name FROM table_name AS t;
SUBQUERY	A query nested inside another query. SELECT * FROM table_name WHERE column IN (SELECT column FROM other_table);
WITH (CTE)	Common Table Expression for temporary result sets. WITH cte_name AS (   SELECT ... ) SELECT * FROM cte_name;

Joins Quick Reference

Join Type	Description / Example Syntax
INNER JOIN	Returns rows that have matching values in both tables. SELECT t1.*, t2.* FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;
LEFT (OUTER) JOIN	Returns all rows from the left table, and matched rows from the right table. SELECT t1.*, t2.* FROM table1 t1 LEFT JOIN table2 t2 ON t1.id = t2.id;
RIGHT (OUTER) JOIN	Returns all rows from the right table, and matched rows from the left table. SELECT t1.*, t2.* FROM table1 t1 RIGHT JOIN table2 t2 ON t1.id = t2.id;
FULL (OUTER) JOIN	Returns rows when there is a match in one of the tables. SELECT t1.*, t2.* FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.id = t2.id;
CROSS JOIN	Returns the Cartesian product of both tables. SELECT t1.*, t2.* FROM table1 t1 CROSS JOIN table2 t2;
SELF JOIN	Joins a table to itself. SELECT a.*, b.* FROM table_name a JOIN table_name b ON a.some_col = b.some_col;

Window Functions & Analytical Queries

Function	Description / Example Syntax
ROW_NUMBER()	Assigns a unique sequential integer to rows within a partition. SELECT ROW_NUMBER() OVER (ORDER BY column) AS rn, * FROM table_name;
RANK() / DENSE_RANK()	Assigns a rank to each row within a partition. SELECT RANK() OVER (ORDER BY column DESC) AS rank_col, * FROM table_name;
LAG() / LEAD()	Accesses data from the preceding (LAG) or following (LEAD) row. SELECT   LAG(column) OVER (ORDER BY date_col) AS prev_val,   LEAD(column) OVER (ORDER BY date_col) AS next_val,   * FROM table_name;
PARTITION BY	Divides the result set into partitions to apply window functions separately within each partition. SELECT column,   ROW_NUMBER() OVER (PARTITION BY category ORDER BY date_col) AS rn FROM table_name;
NTILE(n)	Distributes rows into n buckets. SELECT NTILE(4) OVER (ORDER BY sales DESC) AS quartile, * FROM table_name;

Transaction Control (TCL)

Statement	Description / Example Syntax
COMMIT	Permanently saves the changes made in the current transaction. COMMIT;
ROLLBACK	Undoes changes made in the current transaction. ROLLBACK;
SAVEPOINT	Creates a point within a transaction to which you can roll back. SAVEPOINT savepoint_name; ... ROLLBACK TO savepoint_name;
SET TRANSACTION	Sets transaction properties like isolation level. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Data Control Language (DCL)

Statement	Description / Example Syntax
GRANT	Gives privileges to users/roles. GRANT SELECT, INSERT ON table_name TO user_name;
REVOKE	Removes privileges from users/roles. REVOKE SELECT, INSERT ON table_name FROM user_name;

Constraints & Data Integrity

Constraint	Description / Example Syntax
PRIMARY KEY	Uniquely identifies each row. Only one primary key per table. CREATE TABLE table_name (   id INT PRIMARY KEY,   ... );
FOREIGN KEY	Enforces referential integrity between tables. CREATE TABLE table_name (   fk_id INT,   FOREIGN KEY (fk_id) REFERENCES other_table(id) );
UNIQUE	Ensures all values in a column are distinct. CREATE TABLE table_name (col INT UNIQUE);
NOT NULL	Ensures a column cannot have NULL values. CREATE TABLE table_name (col INT NOT NULL);
CHECK	Ensures values meet specific criteria. CREATE TABLE table_name (   col INT CHECK (col >= 0) );
DEFAULT	Provides a default value if none is specified. CREATE TABLE table_name (col INT DEFAULT 100);

Indexes & Performance

Concept	Description / Example Syntax
CREATE INDEX	Speeds up data retrieval. Impacts write performance. CREATE INDEX index_name ON table_name (column1, [column2, ...]);
UNIQUE INDEX	Ensures index column(s) do not contain duplicates. CREATE UNIQUE INDEX index_name ON table_name (column_name);
CLUSTERED INDEX (SQL Server)	Sorts the table data by the index key. Only one per table (in certain RDBMS like SQL Server).
EXPLAIN / EXPLAIN PLAN	Shows how the database will execute a query, which helps in optimization. EXPLAIN SELECT * FROM table_name;

Views, Stored Procedures & Triggers

Object	Description / Example Syntax
VIEW	A virtual table based on a SELECT query. CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
STORED PROCEDURE	A reusable block of SQL statements stored in the database. CREATE PROCEDURE procedure_name (parameters) BEGIN   -- SQL statements END;
TRIGGER	An action that is automatically invoked after/before an event (INSERT, UPDATE, DELETE). CREATE TRIGGER trigger_name AFTER INSERT ON table_name FOR EACH ROW BEGIN   -- Trigger logic END;

Other Useful Tips

Concept	Description / Example Syntax
Comments	Single-line comment: -- comment Multi-line comment: /* comment */
Multiple Schema Support	Access tables with: schema_name.table_name
Data Types	Examples: INT, BIGINT, DECIMAL VARCHAR(n), CHAR(n), TEXT DATE, TIME, TIMESTAMP BOOLEAN
CASE Expression	Provides conditional logic in a query. SELECT   CASE     WHEN condition THEN 'Value1'     ELSE 'Value2'   END AS alias_name FROM table_name;